Imports nsDataProvider = KDESolutions.Providers.DataProvider
Imports System.Configuration
Imports System.Data.Common

Public Class DBVehicleProvider
    Inherits VehicleProvider
    Private _DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString As String

    Public Const cAccessTrueValue As String = "Y"
    Public Const cAccessFalsevalue As String = "N"

    Public Overrides Sub Initialize(ByVal name As String, ByVal config As System.Collections.Specialized.NameValueCollection)
        If (config Is Nothing) Then
            Throw New ArgumentNullException("config")
        End If
        If String.IsNullOrEmpty(name) Then
            name = "MakeModelProvider"
        End If
        If String.IsNullOrEmpty(config("description")) Then
            config.Remove("description")
            config.Add("description", "The default MakeModel Provider.")
        End If

        'Start of config file settings for database connection ******
        If String.IsNullOrEmpty("DataProviderGroup") Then
            Throw New ArgumentNullException("You must provide a DataProviderGroup")
        Else
            Dim DataProviderGroup As KDESolutions.Providers.DataProvider.DataProviderConfiguration = _
            CType(ConfigurationManager.GetSection(config("DataProviderGroup")), KDESolutions.Providers.DataProvider.DataProviderConfiguration)
            If String.IsNullOrEmpty(DataProviderGroup.DBType) = False Then
                _DBType = DataProviderGroup.DBType
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBServer) = False Then
                _DBServer = DataProviderGroup.DBServer
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBUser) = False Then
                _DBUser = DataProviderGroup.DBUser
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBPassword) = False Then
                _DBPassword = DataProviderGroup.DBPassword
            End If
            If String.IsNullOrEmpty(DataProviderGroup.DBDatabaseName) = False Then
                _DBDatabaseName = DataProviderGroup.DBDatabaseName
            End If
            If String.IsNullOrEmpty(DataProviderGroup.ConnectString) = False Then
                _ConnectString = DataProviderGroup.ConnectString
            End If
        End If

        MyBase.Initialize(name, config)

        'If any configuration attributes are left, throw an exception
        'If ((config.Count > 0) _
        '            AndAlso Not String.IsNullOrEmpty(config.GetKey(0))) Then
        '    Throw New System.Configuration.Provider.ProviderException("There are too many configuration attributes specified")
        'End If
    End Sub

    Public Overrides Function CreateMake(ByVal MakeDescription As String, ByVal LoggedOnUsername As String) As Boolean
        Dim returnVal As Boolean = False
        Try
            Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
            Dim sSQL As String = "INSERT INTO Make(MakeId,MakeDesc,CreateDate,CreatedBy,LastUpdatedDate,LastUpdatedBy,LogicalDeleteInd) " & _
            "VALUES(@MakeId,@MakeDesc,@CreateDate,@CreatedBy,@LastUpdatedDate,@LastUpdatedBy,@LogicalDeleteInd)"
            DAP.AddParameter("@MakeId", System.Guid.NewGuid.ToString, DbType.String)
            DAP.AddParameter("@MakeDesc", MakeDescription, DbType.String)
            DAP.AddParameter("@CreateDate", Now(), DbType.DateTime)
            DAP.AddParameter("@CreatedBy", LoggedOnUsername, DbType.String)
            DAP.AddParameter("@LastUpdatedDate", Now(), DbType.DateTime)
            DAP.AddParameter("@LastUpdatedBy", LoggedOnUsername, DbType.String)
            DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
            If DAP.ExecuteNonQuery(sSQL) = 1 Then
                returnVal = True
            Else
                returnVal = False
            End If
        Catch Ex As Exception
        End Try
        Return returnVal
    End Function

    Public Overrides Function CreateModel(ByVal MakeId As String, ByVal ModelDescription As String, ByVal ModelCategoryId As String, ByVal LoggedOnUsername As String) As Boolean
        Dim returnVal As Boolean = False
        Try
            Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
            Dim sSQL As String = "INSERT INTO Model(ModelId,ModelDesc,ModelCategoryId,MakeId,CreateDate,CreatedBy,LastUpdatedDate,LastUpdatedBy,LogicalDeleteInd) " & _
            "VALUES(@ModelId,@ModelDesc,@ModelCategoryId,@MakeId,@CreateDate,@CreatedBy,@LastUpdatedDate,@LastUpdatedBy,@LogicalDeleteInd)"
            DAP.AddParameter("@ModelId", System.Guid.NewGuid.ToString, DbType.String)
            DAP.AddParameter("@ModelDesc", ModelDescription, DbType.String)
            DAP.AddParameter("@ModelCategoryId", ModelCategoryId, DbType.String)
            DAP.AddParameter("@MakeId", MakeId, DbType.String)
            DAP.AddParameter("@CreateDate", Now(), DbType.DateTime)
            DAP.AddParameter("@CreatedBy", LoggedOnUsername, DbType.String)
            DAP.AddParameter("@LastUpdatedDate", Now(), DbType.DateTime)
            DAP.AddParameter("@LastUpdatedBy", LoggedOnUsername, DbType.String)
            DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
            If DAP.ExecuteNonQuery(sSQL) = 1 Then
                returnVal = True
            Else
                returnVal = False
            End If
        Catch Ex As Exception
            returnVal = False
        End Try
        Return returnVal
    End Function

    Public Overrides Function CreateModelCategory(ByVal CategoryShortDescription As String, ByVal CategoryLongDescription As String, ByVal LoggedOnUsername As String) As Boolean
        Dim returnVal As Boolean = False
        Try
            Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
            Dim sSQL As String = "INSERT INTO ModelCategory(ModelCategoryId,ModelCategoryShortDesc,ModelCategoryLongDesc,CreateDate,CreatedBy,LastUpdatedDate,LastUpdatedBy,LogicalDeleteInd) " & _
            "VALUES(@ModelCategoryId,@ModelCategoryShortDesc,@ModelCategoryLongDesc,@CreateDate,@CreatedBy,@LastUpdatedDate,@LastUpdatedBy,@LogicalDeleteInd)"
            DAP.AddParameter("@ModelCategoryId", System.Guid.NewGuid.ToString, DbType.String)
            DAP.AddParameter("@ModelCategoryShortDesc", CategoryShortDescription, DbType.String)
            DAP.AddParameter("@ModelCategoryLongDesc", CategoryLongDescription, DbType.String)
            DAP.AddParameter("@CreateDate", Now(), DbType.DateTime)
            DAP.AddParameter("@CreatedBy", LoggedOnUsername, DbType.String)
            DAP.AddParameter("@LastUpdatedDate", Now(), DbType.DateTime)
            DAP.AddParameter("@LastUpdatedBy", LoggedOnUsername, DbType.String)
            DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
            If DAP.ExecuteNonQuery(sSQL) = 1 Then
                returnVal = True
            Else
                returnVal = False
            End If
        Catch Ex As Exception
            returnVal = False
        End Try
        Return returnVal
    End Function

    Public Overrides Function DeleteMake(ByVal MakeId As String, ByVal LoggedOnUsername As String) As Boolean
        Dim returnVal As Boolean = False
        Try
            Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
            Dim sSQL As String = "UPDATE Make SET LogicalDeleteInd = @LogicalDeleteInd WHERE MakeId = @MakeId"
            DAP.AddParameter("@LogicalDeleteInd", cAccessTrueValue, DbType.String)
            DAP.AddParameter("@MakeId", MakeId, DbType.String)
            DAP.AddParameter("@LastUpdatedDate", Now(), DbType.DateTime)
            DAP.AddParameter("@LastUpdatedBy", LoggedOnUsername, DbType.String)
            If DAP.ExecuteNonQuery(sSQL) = 1 Then
                returnVal = True
            Else
                returnVal = False
            End If
        Catch Ex As Exception
            returnVal = False
        End Try
        Return returnVal
    End Function

    Public Overrides Function DeleteModel(ByVal ModelId As String, ByVal LoggedOnUsername As String) As Boolean
        Dim returnVal As Boolean = False
        Try
            Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
            Dim sSQL As String = "UPDATE Model SET LogicalDeletInd = @LogicalDeleteInd WHERE ModelId = @ModelId"
            DAP.AddParameter("@LogicalDeleteInd", cAccessTrueValue, DbType.String)
            DAP.AddParameter("@ModelId", ModelId, DbType.String)
            DAP.AddParameter("@LastUpdatedDate", Now(), DbType.DateTime)
            DAP.AddParameter("@LastUpdatedBy", LoggedOnUsername, DbType.String)
            If DAP.ExecuteNonQuery(sSQL) = 1 Then
                returnVal = True
            Else
                returnVal = False
            End If
        Catch Ex As Exception
            returnVal = False
        End Try
        Return returnVal
    End Function

    Public Overrides Function DeleteModelCategory(ByVal ModelCategoryId As String, ByVal LoggedOnUsername As String) As Boolean
        Dim returnVal As Boolean = False
        Try
            Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
            Dim sSQL As String = "UPDATE ModelCategory SET LogicalDeleteInd = @LogicalDeleteInd WHERE ModelCategoryId = @ModelCategoryId"
            DAP.AddParameter("@LogicalDeleteInd", cAccessTrueValue, DbType.String)
            DAP.AddParameter("@ModelCategoryId", ModelCategoryId, DbType.String)
            DAP.AddParameter("@LastUpdatedDate", Now(), DbType.DateTime)
            DAP.AddParameter("@LastUpdatedBy", LoggedOnUsername, DbType.String)
            If DAP.ExecuteNonQuery(sSQL) = 1 Then
                returnVal = True
            Else
                returnVal = False
            End If
        Catch Ex As Exception
            returnVal = False
        End Try
        Return returnVal
    End Function

    Public Overrides Function GetAllMakes() As System.Data.DataTable
        Dim returnVal As New Data.DataTable("Make")
        Try
            Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
            Dim strSQL As String = "SELECT * FROM Make WHERE LogicalDeleteInd = @LogicalDeleteInd ORDER BY MakeDesc"
            DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
            returnVal = DAP.ExecuteDataSet(strSQL).Tables(0)
        Catch Ex As Exception

        End Try
        Return returnVal
    End Function

    Public Overrides Function GetModelCategories() As System.Data.DataTable
        Dim returnVal As New Data.DataTable("ModelCategory")
        Try
            Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
            Dim strSQL As String = "SELECT * FROM ModelCategory WHERE LogicalDeleteInd = @LogicalDeleteInd ORDER BY ModelCategoryShortDesc"
            DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
            returnVal = DAP.ExecuteDataSet(strSQL).Tables(0)
        Catch Ex As Exception

        End Try
        Return returnVal
    End Function

    Public Overrides Function GetModels(ByVal MakeId As String) As System.Data.DataTable
        Dim returnVal As New Data.DataTable("Models")
        Try
            Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
            Dim strSQL As String = "SELECT * FROM Model WHERE MakeId = @MakeId AND LogicalDeleteInd = @LogicalDeleteInd ORDER BY ModelDesc"
            DAP.AddParameter("@MakeId", MakeId, DbType.String)
            DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
            returnVal = DAP.ExecuteDataSet(strSQL).Tables(0)
        Catch Ex As Exception

        End Try
        Return returnVal
    End Function

    Public Overrides Function GetModels(ByVal MakeId As String, ByVal ModelCategoryId As String) As System.Data.DataTable
        Dim returnVal As New Data.DataTable("Models")
        Try
            Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
            Dim strSQL As String = "SELECT * FROM Model WHERE MakeId = @MakeId AND ModelCategoryId = @ModelCategoryId AND LogicalDeleteInd = @LogicalDeleteInd ORDER BY ModelDesc"
            DAP.AddParameter("@MakeId", MakeId, DbType.String)
            DAP.AddParameter("@ModelCategoryId", ModelCategoryId, DbType.String)
            DAP.AddParameter("@LogicalDeleteInd", cAccessFalsevalue, DbType.String)
            returnVal = DAP.ExecuteDataSet(strSQL).Tables(0)
        Catch Ex As Exception

        End Try
        Return returnVal
    End Function

    Public Overrides Function UpdateMake(ByVal MakeId As String, ByVal NewMakeDescription As String, ByVal LoggedOnUsername As String) As Boolean
        Dim returnVal As Boolean = False
        Try
            Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
            Dim sSQL As String = "UPDATE Make SET MakeDesc = @MakeDesc, LastUpdatedDate = @LastUpdatedDate, LastUpdatedBy = @LastUpdatedBy WHERE MakeId = @MakeId"
            DAP.AddParameter("@MakeDesc", NewMakeDescription, DbType.String)
            DAP.AddParameter("@LastUpdatedDate", Now(), DbType.DateTime)
            DAP.AddParameter("@LastUpdatedBy", LoggedOnUsername, DbType.String)
            DAP.AddParameter("@MakeId", MakeId, DbType.String)
            If DAP.ExecuteNonQuery(sSQL) = 1 Then
                returnVal = True
            Else
                returnVal = False
            End If
        Catch Ex As Exception
            returnVal = False
        End Try
        Return returnVal
    End Function

    Public Overrides Function UpdateModel(ByVal ModelId As String, ByVal MakeId As String, ByVal ModelDescription As String, ByVal MOdelCategoryId As String, ByVal LoggedOnUsername As String) As Boolean
        Dim returnVal As Boolean = False
        Try
            Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
            Dim sSQL As String = "UPDATE Model SET ModelDesc = @ModelDesc, ModelCategoryId = @ModelCategoryId, MakeId = @MakeId WHERE ModelId = @ModelId"
            DAP.AddParameter("@ModelDesc", ModelDescription, DbType.String)
            DAP.AddParameter("@MakeId", MakeId, DbType.String)
            DAP.AddParameter("@LastUpdatedDate", Now(), DbType.DateTime)
            DAP.AddParameter("@LastUpdatedBy", LoggedOnUsername, DbType.String)
            If DAP.ExecuteNonQuery(sSQL) = 1 Then
                returnVal = True
            Else
                returnVal = False
            End If
        Catch Ex As Exception
            returnVal = False
        End Try
        Return returnVal
    End Function

    Public Overrides Function UpdateModelCategory(ByVal ModelCategoryId As String, ByVal CategoryShortDescription As String, ByVal CategoryLongDescription As String, ByVal LoggedOnUsername As String) As Boolean
        Dim returnVal As Boolean = False
        Try
            Dim DAP As New nsDataProvider.DataProvider(_DBType, _DBServer, _DBUser, _DBPassword, _DBDatabaseName, _ConnectString)
            Dim sSQL As String = ""
            DAP.AddParameter("@LastUpdatedDate", Now(), DbType.DateTime)
            DAP.AddParameter("@LastUpdatedBy", LoggedOnUsername, DbType.String)
            If DAP.ExecuteNonQuery(sSQL) = 1 Then
                returnVal = True
            Else
                returnVal = False
            End If
        Catch Ex As Exception
            returnVal = False
        End Try
        Return returnVal
    End Function
End Class
